<?php
class ModelAccountCustomer extends Model {
    private $_orderStatusCheck = "AND O.`order_status_id`<>16";
    private $_userCheck = false;//是否能看到所有业务员数据
    private $_customerUserCheck = false;
    private $_orderUserCheck = false;
    private $_userSalesAreaCheck = false;
    private $_thisMonthStart;
    private $_targetMonth;
    private $_thisDayStart;
    private $_lostCycle = 60;
    private $_pieDays = 7;
    private $_specialRange = array(
        20180201 => array('start'=>20180101000000, 'tag'=>201801),
        20180202 => array('start'=>20180101000000, 'tag'=>201801),
        20180203 => array('start'=>20180101000000, 'tag'=>201801),
        20180204 => array('start'=>20180101000000, 'tag'=>201801),
        20180205 => array('start'=>20180101000000, 'tag'=>201801),
        20180206 => array('start'=>20180101000000, 'tag'=>201801),
        20180207 => array('start'=>20180101000000, 'tag'=>201801),
    );

                public function editPasswordById($customer_id, $password) {
					$this->db->query("UPDATE " . DB_PREFIX . "customer SET password = '" . $this->db->escape(md5($password)) . "' WHERE customer_id = '" . (int)$customer_id . "'");
				}

				public function editCustomerById($customer_id, $data) {

					$this->db->query("UPDATE " . DB_PREFIX . "customer SET firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "' WHERE customer_id = '" . (int)$customer_id . "'");
				}

				public function getCustomersMod($data = array()) {
					$sql = "SELECT *, CONCAT(c.firstname, ' ', c.lastname) AS name, cgd.name AS customer_group FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_group_description cgd ON (c.customer_group_id = cgd.customer_group_id) WHERE cgd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

					$implode = array();

					if (!empty($data['filter_name'])) {
						$implode[] = "CONCAT(c.firstname, ' ', c.lastname) LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
					}

					if (!empty($data['filter_email'])) {
						$implode[] = "c.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
					}

					if (isset($data['filter_newsletter']) && !is_null($data['filter_newsletter'])) {
						$implode[] = "c.newsletter = '" . (int)$data['filter_newsletter'] . "'";
					}

					if (!empty($data['filter_customer_group_id'])) {
						$implode[] = "c.customer_group_id = '" . (int)$data['filter_customer_group_id'] . "'";
					}

					if (!empty($data['filter_ip'])) {
						$implode[] = "c.customer_id IN (SELECT customer_id FROM " . DB_PREFIX . "customer_ip WHERE ip = '" . $this->db->escape($data['filter_ip']) . "')";
					}

					if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
						$implode[] = "c.status = '" . (int)$data['filter_status'] . "'";
					}

					if (isset($data['filter_approved']) && !is_null($data['filter_approved'])) {
						$implode[] = "c.approved = '" . (int)$data['filter_approved'] . "'";
					}

					if (!empty($data['filter_date_added'])) {
						$implode[] = "DATE(c.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
					}

					if ($implode) {
						$sql .= " AND " . implode(" AND ", $implode);
					}

					$sort_data = array(
						'name',
						'c.email',
						'customer_group',
						'c.status',
						'c.approved',
						'c.ip',
						'c.date_added'
					);

					if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
						$sql .= " ORDER BY " . $data['sort'];
					} else {
						$sql .= " ORDER BY name";
					}

					if (isset($data['order']) && ($data['order'] == 'DESC')) {
						$sql .= " DESC";
					} else {
						$sql .= " ASC";
					}

					if (isset($data['start']) || isset($data['limit'])) {
						if ($data['start'] < 0) {
							$data['start'] = 0;
						}

						if ($data['limit'] < 1) {
							$data['limit'] = 20;
						}

						$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
					}

					$query = $this->db->query($sql);

					return $query->rows;
				}

			
	public function addCustomer($data) {
		$this->event->trigger('pre.customer.add', $data);

		if (isset($data['customer_group_id']) && is_array($this->config->get('config_customer_group_display')) && in_array($data['customer_group_id'], $this->config->get('config_customer_group_display'))) {
			$customer_group_id = $data['customer_group_id'];
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}

		$this->load->model('account/customer_group');

		$customer_group_info = $this->model_account_customer_group->getCustomerGroup($customer_group_id);

		$this->db->query("INSERT INTO " . DB_PREFIX . "customer SET customer_group_id = '" . (int)$customer_group_id . "', store_id = '" . (int)$this->config->get('config_store_id') . "',user_id='".$this->db->escape($data['user_id'])."', fullname = '" . $this->db->escape($data['fullname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '', custom_field = '" . $this->db->escape(isset($data['custom_field']['account']) ? json_encode($data['custom_field']['account']) : '') . "', salt = '" . $this->db->escape($salt = substr(md5(uniqid(rand(), true)), 0, 9)) . "', password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($data['password'])))) . "', newsletter = '" . (isset($data['newsletter']) ? (int)$data['newsletter'] : 0) . "', ip = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', status = '1', approved = '" . (int)!$customer_group_info['approval'] . "', recommended_code = '" . $this->db->escape($data['recommended_code']) . "', date_added = NOW(),`logcenter_id`='".$this->db->escape($data['logcenter_id'])."'");

		$customer_id = $this->db->getLastId();
		
		//edit mcc
		if(isset($data['address'])) {
			
			$this->db->query("INSERT INTO " . DB_PREFIX . "address SET customer_id = '" . (int)$customer_id . "', fullname = '" . $this->db->escape($data['fullname']) . "', company = '" . $this->db->escape($data['company']) . "', shipping_telephone = '" . $this->db->escape($data['telephone']) . "', address = '" . $this->db->escape($data['address']) . "', city = '" . $this->db->escape($data['city']) . "', postcode = '" . $this->db->escape($data['postcode']) . "', country_id = '" . (int)$data['country_id'] . "', zone_id = '" . (int)$data['zone_id'] . "', custom_field = '" . $this->db->escape(isset($data['custom_field']['address']) ? json_encode($data['custom_field']['address']) : '') . "'");
	
			$address_id = $this->db->getLastId();
	
			$this->db->query("UPDATE " . DB_PREFIX . "customer SET address_id = '" . (int)$address_id . "' WHERE customer_id = '" . (int)$customer_id . "'");
		
		}
		//end mcc

		//delete temporary mobile number
		$this->db->query("DELETE FROM " . DB_PREFIX . "sms_mobile WHERE sms_mobile = '" . $this->db->escape($data['telephone']) . "'");

		$this->load->language('mail/customer');

		$subject = sprintf($this->language->get('text_subject'), html_entity_decode($this->config->get('config_name'), ENT_QUOTES, 'UTF-8'));

		$message = sprintf($this->language->get('text_welcome'), html_entity_decode($this->config->get('config_name'), ENT_QUOTES, 'UTF-8')) . "\n\n";

		if (!$customer_group_info['approval']) {
			$message .= $this->language->get('text_login') . "\n";
		} else {
			$message .= $this->language->get('text_approval') . "\n";
		}

		$message .= $this->url->link('account/login', '', 'SSL') . "\n\n";
		$message .= $this->language->get('text_services') . "\n\n";
		$message .= $this->language->get('text_thanks') . "\n";
		$message .= html_entity_decode($this->config->get('config_name'), ENT_QUOTES, 'UTF-8');

		$mail = new Mail();
		$mail->protocol = $this->config->get('config_mail_protocol');
		$mail->parameter = $this->config->get('config_mail_parameter');
		$mail->smtp_hostname = $this->config->get('config_mail_smtp_hostname');
		$mail->smtp_username = $this->config->get('config_mail_smtp_username');
		$mail->smtp_password = html_entity_decode($this->config->get('config_mail_smtp_password'), ENT_QUOTES, 'UTF-8');
		$mail->smtp_port = $this->config->get('config_mail_smtp_port');
		$mail->smtp_timeout = $this->config->get('config_mail_smtp_timeout');
			
		$mail->setTo($data['email']);
		$mail->setFrom($this->config->get('config_email'));
		$mail->setSender(html_entity_decode($this->config->get('config_name'), ENT_QUOTES, 'UTF-8'));
		$mail->setSubject($subject);
		$mail->setText($message);
		$mail->send();

		// Send to main admin email if new account email is enabled
		if ($this->config->get('config_account_mail')) {
			$message  = $this->language->get('text_signup') . "\n\n";
			$message .= $this->language->get('text_website') . ' ' . html_entity_decode($this->config->get('config_name'), ENT_QUOTES, 'UTF-8') . "\n";
			$message .= $this->language->get('text_fullname') . ' ' . $data['fullname'] . "\n";
			$message .= $this->language->get('text_customer_group') . ' ' . $customer_group_info['name'] . "\n";
			$message .= $this->language->get('text_email') . ' '  .  $data['email'] . "\n";
			$message .= $this->language->get('text_telephone') . ' ' . $data['telephone'] . "\n";

			$mail = new Mail();
			$mail->protocol = $this->config->get('config_mail_protocol');
			$mail->parameter = $this->config->get('config_mail_parameter');
			$mail->smtp_hostname = $this->config->get('config_mail_smtp_hostname');
			$mail->smtp_username = $this->config->get('config_mail_smtp_username');
			$mail->smtp_password = html_entity_decode($this->config->get('config_mail_smtp_password'), ENT_QUOTES, 'UTF-8');
			$mail->smtp_port = $this->config->get('config_mail_smtp_port');
			$mail->smtp_timeout = $this->config->get('config_mail_smtp_timeout');
			
			$mail->setTo($this->config->get('config_email'));
			$mail->setFrom($this->config->get('config_email'));
			$mail->setSender(html_entity_decode($this->config->get('config_name'), ENT_QUOTES, 'UTF-8'));
			$mail->setSubject(html_entity_decode($this->language->get('text_new_customer'), ENT_QUOTES, 'UTF-8'));
			$mail->setText($message);
			$mail->send();

			// Send to additional alert emails if new account email is enabled
			$emails = explode(',', $this->config->get('config_mail_alert'));

			foreach ($emails as $email) {
				if (utf8_strlen($email) > 0 && preg_match('/^[^\@]+@.*.[a-z]{2,15}$/i', $email)) {
					$mail->setTo($email);
					$mail->send();
				}
			}
		}

		$this->event->trigger('post.customer.add', $customer_id);

		return $customer_id;
	}

	public function editCustomer($data) {
		$this->event->trigger('pre.customer.edit', $data);

		$customer_id = $this->customer->getId();

		$this->db->query("UPDATE " . DB_PREFIX . "customer SET fullname = '" . $this->db->escape($data['fullname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "', custom_field = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : '') . "' WHERE customer_id = '" . (int)$customer_id . "'");
		
		//delete temporary mobile number
		$this->db->query("DELETE FROM " . DB_PREFIX . "sms_mobile WHERE sms_mobile = '" . $this->db->escape($data['telephone']) . "'");

		$this->event->trigger('post.customer.edit', $customer_id);
	}

	public function neweditCustomer($data) {
		$this->event->trigger('pre.customer.edit', $data);

		$customer_id = $this->customer->getId();

		$sql = "UPDATE " . DB_PREFIX . "customer SET fullname = '" . $this->db->escape($data['fullname']);
		if(!empty($data['avatar'])){
			$sql .= "', avatar = '" . $this->db->escape($data['avatar']);
		}
		if(!empty($data['gender'])){
			$sql .= "', gender = '" . $this->db->escape($data['gender']);
		}
		if(!empty($data['license_image'])){
			$sql .= "', license_image = '" . $this->db->escape($data['license_image']);
		}
		if(!empty($data['company_name'])){
			$sql .= "', company_name = '" . $this->db->escape($data['company_name']);
		}
		if(!empty($data['shop_type'])){
			$sql .= "', shop_type = '" . (int)$data['shop_type'];
            if (8 == $data['shop_type']) {//终端类型为“正规标准连锁”时，强制修改客户分组，看到另一套价格体系。
                $sql .= "', customer_group_id='2";
            }
		}
		$sql .= "',custom_field = '" . $this->db->escape(isset($data['custom_field']) ? json_encode($data['custom_field']) : '') . "' WHERE customer_id = '" . (int)$customer_id . "'";

		$this->db->query($sql);

		//保存用户上传之图片信息
		$CustomerImages = M('customer_images');
		$CustomerImages->where(array('customer_id'=>$customer_id))->delete();
		foreach ($data['license_code'] as $cd) {
			$CustomerImages->data(array('customer_id'=>$customer_id, 'code'=>$cd, 'type'=>2, 'date_added'=>date('Y-m-d H:i:s')))->add();
		}
		foreach ($data['shop_code'] as $cd) {
			$CustomerImages->data(array('customer_id'=>$customer_id, 'code'=>$cd, 'date_added'=>date('Y-m-d H:i:s')))->add();
		}

		//delete temporary mobile number
		//$this->db->query("DELETE FROM " . DB_PREFIX . "sms_mobile WHERE sms_mobile = '" . $this->db->escape($data['telephone']) . "'");

		$this->event->trigger('post.customer.edit', $customer_id);
	}

	public function editPassword($email, $password) {
		$this->event->trigger('pre.customer.edit.password');

		$this->db->query("UPDATE " . DB_PREFIX . "customer SET salt = '" . $this->db->escape($salt = token(9)) . "', password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($password)))) . "' WHERE LOWER(email) = '" . $this->db->escape(utf8_strtolower($email)) . "'");

		$this->event->trigger('post.customer.edit.password');
	}

	public function editPasswordwithphone($telephone, $password) {
		$this->event->trigger('pre.customer.edit.password');

		$this->db->query("UPDATE " . DB_PREFIX . "customer SET salt = '" . $this->db->escape($salt = token(9)) . "', password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($password)))) . "' WHERE telephone = '" . $this->db->escape($telephone) . "'");

		$this->event->trigger('post.customer.edit.password');
	}


	public function editNewsletter($newsletter) {
		$this->event->trigger('pre.customer.edit.newsletter');

		$this->db->query("UPDATE " . DB_PREFIX . "customer SET newsletter = '" . (int)$newsletter . "' WHERE customer_id = '" . (int)$this->customer->getId() . "'");

		$this->event->trigger('post.customer.edit.newsletter');
	}

	public function getCustomer($customer_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$customer_id . "'");

		return $query->row;
	}

	public function getCustomerImages($customer_id) {
		$query = M('customer_images ci')
		->join('upload u on u.code=ci.code')
		->field('u.code as code, u.filename, ci.type')
		->where('ci.customer_id="' . $customer_id . '"')
		->select();

		return $query;
	}

	public function getCustomerByEmail($email) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE (LOWER(email) = '" . $this->db->escape(utf8_strtolower($email)) . "' OR LOWER(telephone) = '" . $this->db->escape(utf8_strtolower($email)) . "')");

		return $query->row;
	}

	public function getMyUserId($email) {
		$query = $this->db->query("SELECT user_id FROM " . DB_PREFIX . "user WHERE (LOWER(contact_tel) = '" . $this->db->escape(utf8_strtolower($email)) . "')");

		return $query->row['user_id'];
	}

	public function getCustomerByTelephone($telephone) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE LOWER(telephone) = '" . $this->db->escape(utf8_strtolower($telephone)) . "'");

		return $query->row;
	}


	public function getCustomerByToken($token) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer WHERE token = '" . $this->db->escape($token) . "' AND token != ''");

		$this->db->query("UPDATE " . DB_PREFIX . "customer SET token = ''");

		return $query->row;
	}

	public function getTotalCustomersByEmail($email) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE LOWER(email) = '" . $this->db->escape(utf8_strtolower($email)) . "'");

		return $query->row['total'];
	}
	
	public function getRewardTotal($customer_id) {
		$query = $this->db->query("SELECT SUM(points) AS total FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . (int)$customer_id . "'");

		return $query->row['total'];
	}

	public function getIps($customer_id) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_ip` WHERE customer_id = '" . (int)$customer_id . "'");

		return $query->rows;
	}

	public function addLoginAttempt($email) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "customer_login WHERE email = '" . $this->db->escape(utf8_strtolower((string)$email)) . "' AND ip = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "'");
		
		if (!$query->num_rows) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "customer_login SET email = '" . $this->db->escape(utf8_strtolower((string)$email)) . "', ip = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', total = 1, date_added = '" . $this->db->escape(date('Y-m-d H:i:s')) . "', date_modified = '" . $this->db->escape(date('Y-m-d H:i:s')) . "'");
		} else {
			$this->db->query("UPDATE " . DB_PREFIX . "customer_login SET total = (total + 1), date_modified = '" . $this->db->escape(date('Y-m-d H:i:s')) . "' WHERE customer_login_id = '" . (int)$query->row['customer_login_id'] . "'");
		}			
	}	
	
	public function getLoginAttempts($email) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_login` WHERE email = '" . $this->db->escape(utf8_strtolower($email)) . "'");

		return $query->row;
	}
	
	public function deleteLoginAttempts($email) {
		$this->db->query("DELETE FROM `" . DB_PREFIX . "customer_login` WHERE email = '" . $this->db->escape(utf8_strtolower($email)) . "'");
	}
	
	
	public function getTotalCustomersByTelephone($telephone) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE telephone = '" . $this->db->escape($telephone) . "'");

		return $query->row['total'];
	}

	public function saveLicenseImage($customer_id, $license_image) {
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET license_image = '" . $this->db->escape($license_image) . "' WHERE customer_id = '" . (int)$customer_id . "'");
	}

	public function saveWxOpenid($customer_id, $wx_openid) {
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET wx_openid = '" . $this->db->escape($wx_openid) . "' WHERE customer_id = '" . (int)$customer_id . "'");
	}

	public function saveAvatar($customer_id, $avatar) {
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET avatar = '" . $this->db->escape($avatar) . "' WHERE customer_id = '" . (int)$customer_id . "'");
	}

	public function saveRecommendedCode($customer_id, $recommended_code, $ground_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET recommended_code = '" . $this->db->escape($recommended_code) . "', ground_id = '" . (int)$ground_id . "' WHERE customer_id = '" . (int)$customer_id . "'");
	}

	public function getGroundIdByCode($recommended_code) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "ground` WHERE code = '" . $this->db->escape($recommended_code) . "'");

		return $query->row;
	}

	public function saveCard($data) {
		$Cards = M('cards');
		$card = $Cards->where(array('storable_card_no'=>$data['storable_card_no']))->find();
		if(!$card) {
			$Cards->data($data)->add();	
		}
	}

	public function getCards($customer_id) {
		$Cards = M('cards');
		$all_cards = $Cards->where(array('customer_id'=>$customer_id))->select();
		return $all_cards;
	}

	public function getCustomerCoupons($customer_id) {
		$map['customer_id'] = $customer_id;
		$query = M('customer_coupon')->where($map)->field('coupon_id')->select();
		$coupon_ids = array();
		foreach ($query as $key => $coupon) {
			$coupon_ids[] = $coupon['coupon_id'];
		}
		if(!empty($coupon_ids)) {
			$query2 = M('coupon')->where(array('coupon_id'=>array('in', $coupon_ids)))->order('date_added desc')->field('code')->select();	
		} else {
			$query2 = array();
		}
		
		$coupons = array();
		$this->load->model('checkout/coupon');
		foreach ($query2 as $key => $coupon) {
			$coupon_info = $this->model_checkout_coupon->getCoupon2($coupon['code']);
			if($coupon_info) {
				$coupon_info['range'] = '全场通用';
				$coupons[] = $coupon_info;
			}
		}
		return $coupons;
	}

	public function getCustomerCouponsForCheckout($customer_id) {
		$map['customer_id'] = $customer_id;
		$query = M('customer_coupon')->where($map)->field('coupon_id')->select();
		$coupon_ids = array();
		foreach ($query as $key => $coupon) {
			$coupon_ids[] = $coupon['coupon_id'];
		}
		if(!empty($coupon_ids)) {
			$query2 = M('coupon')->where(array('coupon_id'=>array('in', $coupon_ids)))->order('date_added desc')->field('code')->select();	
		} else {
			$query2 = array();
		}
		
		$coupons = array();
		$this->load->model('checkout/coupon');
		foreach ($query2 as $key => $coupon) {
			$coupon_info = $this->model_checkout_coupon->getCoupon($coupon['code']);
			if($coupon_info) {
				$coupon_info['range'] = '全场通用';
				$coupons[] = $coupon_info;
			}
		}
		return $coupons;
	}

	public function getAvaliableCustomerCoupon($customer_id) {
		$coupons = $this->getCustomerCouponsForCheckout($customer_id);
		// var_dump($coupons);
		$max_coupon = array('discount'=>0);
		if($coupons) {
			foreach ($coupons as $coupon) {
				// var_dump($coupon['discount'].'c');
				// var_dump($max_coupon['discount'].'m');
				if($coupon['discount']>$max_coupon['discount']) {
					$max_coupon = $coupon;
				}
			}
			return $max_coupon;
		}
		return array();
	}

	public function saveVip($telephone,$user_id){
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET yiji_userid = '" . (string)$user_id . "' WHERE telephone = '" . (int)$telephone . "'");
	}

	public function setIsCheck($customer_id){
		$query = $this->db->query("UPDATE " . DB_PREFIX . "customer SET is_check = 1 WHERE customer_id = '" . (int)$customer_id . "'");
		return $query;
	}

    /* 业务员报表相关 @author sonicsjh */
    /*
     * 检查 user.contact_tel，判断是否业务员，返回业务员编号
     */
    public function userCheckByTelephone($telephone) {
        //return 106;
        $ret = array(
            'MG_userId' => 0,//user表pk，记录操作人信息
            'MG_saleId' => 0,//业务员pk，业务员角色获取订单、客户、数据报表
            'MG_logcenterId' => 0,//物流中心pk，仓管角色获取订单、出库单
        );
        $sql = "SELECT `user_id`,`user_group_id`,`logcenter_permission` FROM `".DB_PREFIX."user` WHERE `contact_tel`='".$telephone."'";
        $query = $this->db->query($sql);
        if ($query->num_rows) {
            $ret['MG_userId'] = $query->row['user_id'];
            $ret['MG_logcenterId'] = $query->row['logcenter_permission'];
            if (63 == $query->row['user_group_id']) {
                $ret['MG_saleId'] = $query->row['user_id'];
            }
        }
        return $ret;
    }

    protected function _specialCheck() {
        $today = date('Ymd');
        if (array_key_exists($today, $this->_specialRange)) {
            $this->_thisMonthStart = $this->_specialRange[$today]['start'];
            $this->_targetMonth = $this->_specialRange[$today]['tag'];
        }else{
            $this->_thisMonthStart = date('Ym').'01000000';
            $this->_targetMonth = date('Ym');
        }
    }

    /*
     * 获取业务员个人报表汇总信息
     */
    public function getSaleGatherInfo($user) {
        $gatherInfo = array();
        $this->_specialCheck();
        $this->_thisDayStart = date('Ymd').'000000';
        if ($user['MG_saleId']) {
            $this->_userCheck = true;
            $this->_customerUserCheck = " AND C.`user_id`='".$user['MG_saleId']."'";
            $this->_orderUserCheck = " AND O.`recommend_usr_id`='".$user['MG_saleId']."'";
        }
        return $this->_initGatherInfo();
    }

    /*
     * 获取业务员报表汇总信息
     */
    public function getSaleGatherInfoInArea($areaId) {
        $gatherInfo = array();
        $this->_specialCheck();
        $this->_thisDayStart = date('Ymd').'000000';
        $this->_userCheck = true;
        if ($areaId) {
            $this->_customerUserCheck = " AND C.`user_id` IN (SELECT `user_id` FROM `".DB_PREFIX."user` WHERE `user_group_id`='63' AND `sales_area_id`='".$areaId."')";
            $this->_orderUserCheck = " AND O.`recommend_usr_id` IN (SELECT `user_id` FROM `".DB_PREFIX."user` WHERE `user_group_id`='63' AND `sales_area_id`='".$areaId."')";
            $this->_userSalesAreaCheck = " AND U.`sales_area_id`='".$areaId."'";
        }

        return $this->_initGatherInfo();
    }

    /*
     * 格式化报表信息
     */
    protected function _initGatherInfo($areaId) {
        $ret = array();
        $ret['orderedNowRate'] = $this->_getOrderedNowRate();
        $ret['reOrderedNowRate'] = $this->_getReOrderedNowRate();
        $ret['amountReceiptWithoutPayed'] = $this->_getAmountReceiptWithoutPayed();
        //$ret['lostRate'] = $this->_getLostRate();//60天客户流失率
        $ret['tPriceNow'] = $this->_getTPriceNow();
        $ret['tPriceTargetNow'] = $this->_getTPriceTargetNow();
        $ret['tPriceFinishRateNow'] = $this->_getTPriceFinishRateNow($ret['tPriceNow'], $ret['tPriceTargetNow']);
        $ret['bar'] = $this->_getBarData();
        $ret['pie'] = $this->_getPieData();
        $ret['userGatherInfo'] = $this->_getUserGatherInfo();
        return $ret;
    }

    /*
     * 计算业务员当月转化率
     */
    protected function _getOrderedNowRate() {
        $ret = 0;
        if ($this->_userCheck) {
            $orderedNow = $this->_getOrderedNow();
            $registerAll = $this->_getRegisterAll();
            if ($registerAll) {
                $ret = round(($orderedNow*100/$registerAll), 0);
            }
        }
        return $ret;
    }

    /*
     * 计算业务员当月复购率
     */
    protected function _getReOrderedNowRate() {
        $ret = 0;
        if ($this->_userCheck) {
            $orderedNow = $this->_getOrderedNow();
            $orderedAll = $this->_getOrderedAll();
            if ($orderedAll) {
                $ret = round(($orderedNow*100/$orderedAll), 0);
            }
        }
        return $ret;
    }

    /*
     * 计算客户流失率（废弃）
     */
    protected function _getLostRate() {
        $ret = 0;
        if ($this->_userCheck) {
            $noOrderInCycle = $this->_getNoOrderInCycle();
            $orderedAll = $this->_getRegisterAll();
            if ($orderedAll) {
                $ret = round(($noOrderInCycle*100/$orderedAll), 0);
            }
        }
        return $ret;
    }

    /*
     * 统计客户已收货未付款总金额
     */
    protected function _getAmountReceiptWithoutPayed() {//【仅统计2017.10.01以后的订单】
        $ret = 0;
        if ($this->_userCheck) {
            $sql = "SELECT SUM(`sale_money`) AS total FROM `stock_out` AS SO LEFT JOIN `order` AS O ON (SO.`refer_id`=O.`order_id`) LEFT JOIN `customer` AS C ON (C.`customer_id`=O.`customer_id`) WHERE SO.`refer_type_id`='1' AND SO.`status`='4' ".$this->_orderStatusCheck." AND O.`payment_code`='cod' AND O.`is_pay`='0' AND O.`order_id`!='7103' AND O.`date_added`>'20171001000000'".$this->_orderUserCheck;
            $query = $this->db->query($sql);
            $ret = round($query->row['total'], 2);
        }
        return $ret;
    }

    /*
     * 获取业务员本月销售总额
     */
    protected function _getTPriceNow() {
        $ret = 0;
        if ($this->_userCheck) {
            //$sql = "SELECT SUM(`total`) AS total FROM `".DB_PREFIX."order` WHERE `order_status_id`<>16 AND `customer_id` IN (SELECT C.`customer_id` FROM `".DB_PREFIX."customer` AS C WHERE 1".$this->_customerUserCheck.") AND `date_added`>='".$this->_thisMonthStart."'";
            $sql = "SELECT SUM(`total`) AS total FROM `".DB_PREFIX."order` AS O WHERE O.`date_added`>='".$this->_thisMonthStart."'".$this->_orderStatusCheck.$this->_orderUserCheck;
            $query = $this->db->query($sql);
            $ret = round($query->row['total'], 2);
        }
        return $ret;
    }

    /*
     * 获取业务员本月目标销售额
     */
    protected function _getTPriceTargetNow() {
        $ret = 0;
        if ($this->_userCheck) {
            $sql = "SELECT SUM(C.`target_amount`) AS TA FROM `".DB_PREFIX."sales_target` AS C WHERE C.`target_date`='".$this->_targetMonth."'".$this->_customerUserCheck;
            // echo $sql;
            $query = $this->db->query($sql);
            $ret = round($query->row['TA'], 2);
        }
        return $ret;
    }
    
    /*
     * 计算业务员本月销售额完成率
     */
    protected function _getTPriceFinishRateNow($tPriceNow, $tPriceTargetNow) {
        $ret = 100;
        if ($tPriceTargetNow) {
            $ret = round($tPriceNow*100/$tPriceTargetNow, 2);
        }else if (0 == (int)$tPriceNow){
            $ret = 0;
        }
        return (float)$ret;
    }

    /*
     * 获取业务员最近 N 天的订单笔数（柱状图）
     */
    protected function _getBarData($userId) {
        return array(
            'legendData' => '',
            'xData' => $this->_getLastDays(),
            'series' => array(
                array(
                    'name' => '',
                    'type' => 'bar',
                    'label' => array(
                        'normal' => array(
                            'show' => false,
                        ),
                        'emphasis' => array(
                            'show' => true,
                            'position' => 'top',
                            'distance' => '0',
                            'formatter' => '{c}',
                        ),
                    ),
                    'data' => $this->_getBarSeriesData(),
                ),
            ),
        );
    }

    /*
     * 获取业务员所有下单客户结构（饼图）
     */
    protected function _getPieData() {
        return array(
            'legendData' => array_values(getShopTypes()),
            'seriesData' => $this->_getPieSeriesData(),
        );
    }

    /*
     * 获取业务员当日订单数量和订单总金额（仅显示在区域报表内）
     */
    protected function _getUserGatherInfo() {
        $ret = array();
        if ($this->_userCheck) {
            $sql = "SELECT ST.`user_id`,U.`fullname` FROM `".DB_PREFIX."sales_target` AS ST LEFT JOIN `".DB_PREFIX."user` AS U ON (ST.`user_id`=U.`user_id`) WHERE ST.`target_date`='".$this->_targetMonth."'".$this->_userSalesAreaCheck;
            // echo $sql;
            $query = $this->db->query($sql);
            foreach ($query->rows as $row) {
                $ret[$row['user_id']] = array(
                    'fullName' => $row['fullname'],
                    'orderNums' => 0,
                    'orderTotalPrice'=> 0,
                );
            }
            $sql = "SELECT U.`user_id`,U.`fullname`,COUNT(`order_id`) AS ONS,SUM(`total`) as OTP FROM `".DB_PREFIX."order` AS O LEFT JOIN `".DB_PREFIX."user` AS U ON (O.`recommend_usr_id`=U.`user_id`) WHERE O.`date_added`>='".$this->_thisDayStart."'".$this->_orderStatusCheck.$this->_orderUserCheck." GROUP BY O.`recommend_usr_id`";
            // echo $sql;
            $query = $this->db->query($sql);
            foreach ($query->rows as $row) {
                // if (array_key_exists($row['user_id'], $ret)) {
                    $ret[$row['user_id']]['orderNums'] = intval($row['ONS']);
                    $ret[$row['user_id']]['fullName'] = $row['fullname'];
                    $ret[$row['user_id']]['orderTotalPrice'] = round($row['OTP'], 2);
                // }
            }
        }
        // var_dump($ret);
        return $ret;
    }

    /*
     * 获取当月下单用户数
     */
    private function _getOrderedNow() {
        //$sql = "SELECT COUNT(`customer_id`) AS nums FROM `".DB_PREFIX."customer` AS C WHERE EXISTS (SELECT `order_id` FROM `".DB_PREFIX."order` AS O WHERE O.`customer_id`=C.`customer_id` ".$this->_orderStatusCheck." AND O.`date_added`>='".$this->_thisMonthStart."')".$this->_customerUserCheck;
        $sql = "SELECT COUNT(`customer_id`) AS nums FROM `".DB_PREFIX."order` AS O WHERE O.`date_added`>='".$this->_thisMonthStart."'".$this->_orderStatusCheck.$this->_orderUserCheck;
        $query = $this->db->query($sql);
        return (int)$query->row['nums'];
    }

    /*
     * 获取总注册用户数
     */
    private function _getRegisterAll() {
        $sql = "SELECT COUNT(C.`customer_id`) AS nums FROM `".DB_PREFIX."customer` AS C WHERE 1".$this->_customerUserCheck;
        $query = $this->db->query($sql);
        return (int)$query->row['nums'];
        
    }

    /*
     * 获取总下单用户数
     */
    private function _getOrderedAll() {
        //$sql = "SELECT COUNT(`customer_id`) AS nums FROM `".DB_PREFIX."customer` AS C WHERE EXISTS (SELECT `order_id` FROM `".DB_PREFIX."order` AS O WHERE O.`customer_id`=C.`customer_id` ".$this->_orderStatusCheck.")".$this->_customerUserCheck;
        $sql = "SELECT COUNT(`customer_id`) AS nums FROM `".DB_PREFIX."order` AS O WHERE 1".$this->_orderStatusCheck.$this->_orderUserCheck;
        $query = $this->db->query($sql);
        return (int)$query->row['nums'];
    }

    /*
     * 获取周期时间内未下单用户数
     */
    private function _getNoOrderInCycle() {
        $sql = "SELECT COUNT(`customer_id`) AS nums FROM `".DB_PREFIX."customer` AS C WHERE C.`customer_id` NOT IN (SELECT `customer_id` FROM `".DB_PREFIX."order` AS O WHERE 1 ".$this->_orderStatusCheck." AND O.`date_added`>'".date('Ymd', strtotime('-'.$this->_lostCycle.' Days'))."000000')".$this->_customerUserCheck;
        $query = $this->db->query($sql);
        return (int)$query->row['nums'];
    }

    /*
     * 获取柱状图显示数据
     */
    private function _getBarSeriesData() {
        $ret = $temp = array();
        if ($this->_userCheck) {
            //$sql = "SELECT LEFT(`date_added`, 10) AS date,COUNT(`order_id`) AS nums FROM `".DB_PREFIX."order` WHERE `order_status_id`<>16 AND `customer_id` IN (SELECT C.`customer_id` FROM `".DB_PREFIX."customer` AS C WHERE 1".$this->_customerUserCheck.") AND `date_added`>='".date('Ymd', strtotime('-'.$this->_pieDays.' Days'))."000000' GROUP BY LEFT(`date_added`, 10)";
            $sql = "SELECT LEFT(O.`date_added`, 10) AS date,COUNT(O.`order_id`) AS nums FROM `".DB_PREFIX."order` AS O WHERE O.`date_added`>='".date('Ymd', strtotime('-'.$this->_pieDays.' Days'))."000000'".$this->_orderStatusCheck.$this->_orderUserCheck." GROUP BY LEFT(O.`date_added`, 10)";
            $query = $this->db->query($sql);
            foreach ($query->rows as $row) {
                $temp[$row['date']] = $row['nums'];
            }
            $dateList = $this->_getLastDays('Y-m-d');
            foreach ($dateList as $date) {
                $ret[] = (int)$temp[$date];
            }
        }
        return $ret;
    }

    /*
     * 获取饼图显示数据
     */
    private function _getPieSeriesData() {
        $ret = $temp = array();
        if ($this->_userCheck) {
            $sql = "SELECT `shop_type`,COUNT(`customer_id`) AS nums FROM `".DB_PREFIX."customer` AS C WHERE EXISTS (SELECT `order_id` FROM `".DB_PREFIX."order` AS O WHERE O.`customer_id`=C.`customer_id` ".$this->_orderStatusCheck.")".$this->_customerUserCheck." GROUP BY C.`shop_type`";
            $query = $this->db->query($sql);
            foreach ($query->rows as $row) {
                $temp[$row['shop_type']] = $row['nums'];
            }
        }
        foreach (getShopTypes() as $shopTypeId=>$shopTypeName) {
            $ret[] = array(
                'value' => $temp[$shopTypeId],
                'name' => $shopTypeName,
            );
        }
        return $ret;
    }

    /*
     * 获取最近 N 天日期列表
     */
    private function _getLastDays($format='m.d') {
        $ret = array();
        $c = $this->_pieDays;
        while ($c) {
            $ret[] = date($format, strtotime('-'.$c.' days'));
            $c--;
        }
        return $ret;
    }
    /* 业务员报表相关 @author sonicsjh */
}